﻿/*
#------------------------------------------------------------------------------
#-- View Name:		[dbo].[vwWH_LogIndex]
#-- Purpose:		Provides a method for reviewing index maintenance, and
#--					compare the use of REBUILD vs REORGANIZE
#--	Last Update:	03/22/2013
#--					For a complete history - please review comments in SVN
#------------------------------------------------------------------------------
*/
CREATE VIEW [dbo].[vwWH_LogIndex]
AS

SELECT		ssd.ssd_sqlserver_name_ro,
			mnt_table_catalog,
			mnt_table_schema,
			mnt_table_name,
			SUBSTRING(SUBSTRING(mnt_results, 0, CHARINDEX('] ON [', mnt_results)), CHARINDEX('ALTER INDEX [', mnt_results) + 13, CHARINDEX('---', mnt_results)) mnt_index_name,
			mnt_action_date,
			CAST(CASE WHEN CHARINDEX('REBUILD', mnt_results, 0) > 0 THEN 1 ELSE 0 END as bit) mnt_action_is_rebuild,
			mnt_success,
			LTRIM(RTRIM(REPLACE(SUBSTRING(mnt_results, CHARINDEX('---', mnt_results), 8000), '---', ''))) mnt_comments
FROM		[dbo].[tblWH_Log] mnt (NOLOCK)
JOIN		[dbo].[tblMSX_server_discovery] ssd ON mnt.mnt_server_ssd_id = ssd.ssd_id
WHERE		mnt_action_type = 'index'
